import os
import pandas as pd
from datetime import datetime


class ExcelOp(object):
    __instance = None
    __is_init = False

    def __new__(cls, *args, **kwargs):
        if cls.__instance is None:
            cls.__instance = super().__new__(cls)
        return cls.__instance

    def __init__(self):
        if self.__is_init is False:
            self._root = os.path.dirname(os.path.abspath(os.path.realpath(__file__)))

            self.dirname = os.path.join(os.path.dirname(self._root), "course")
            if not os.path.isdir(self.dirname):
                os.mkdir(self.dirname)

            self._excel_ext = ['.xls', '.xlsx']

    @classmethod
    def search_df(cls, start_date, end_date) -> pd.DataFrame:
        excel_df = cls.excel_info()

        bool = excel_df['date'].apply(lambda x: True if isinstance(x, datetime) else False)

        excel_df = excel_df[bool]
        excel_df = excel_df[excel_df['date'].notnull()]
        excel_df = excel_df[
            (excel_df['date'] >= pd.to_datetime(start_date)) & (excel_df['date'] <= pd.to_datetime(end_date))]

        teacher = excel_df[excel_df['teacher'].notnull()][['date', 'teacher']]
        coach = excel_df[excel_df['coach'].notnull()][['date', 'coach']]
        coach.rename(columns={'coach': 'teacher'}, inplace=True)
        df = pd.concat([teacher, coach]) # 合并多个df

        # 重置索引
        df.index = range(df.shape[0])
        return pd.DataFrame(df)

    @classmethod
    def excel_info(cls) -> pd.DataFrame:
        op = cls()
        excel_files = op.files_list()
        dfs = []
        for f in excel_files:
            tmp_path = os.path.join(op.dirname, f)
            tmp_df = pd.read_excel(tmp_path, header=None, skiprows=1, usecols=[0, 1, 2, 3, 4, 5], parse_dates=True)
            tmp_df.columns = ["date", "week", "course", "teacher", "brief", "coach"]
            dfs.append(tmp_df)
        new_df = pd.concat(dfs)
        return new_df

    def files_list(self):
        files = os.listdir(self.dirname)
        if len(files) == 0:
            raise Exception("请在course目录下添加课表！")
        op_files = [f for f in files if os.path.splitext(f)[1] in self._excel_ext]
        return op_files


if __name__ == '__main__':
    # eo = ExcelOp()
    start_date = "2019-10-10"
    end_date = "2019-12-30"
    # print(start)
    # print(end)
    # ExcelOp.excel_info()
    df = ExcelOp.search_df(start_date=start_date, end_date=end_date)
